package net.ys.dao;

import net.ys.bean.KjResult;
import org.apache.tomcat.util.buf.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author: NMY
 * @Date: 2021/6/22
 * @Time: 20:51
 */
@Repository
public class ApiDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public boolean buy(List<List<Integer>> nums, String now) {
        String sql = "INSERT INTO buy_record (`buy_date`, `red_1`, `red_2`, `red_3`, `red_4`,`red_5`,`red_6`,`blue`) VALUES (?,?,?,?,?,?,?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                List<Integer> num = nums.get(i);
                ps.setString(1, now);
                ps.setInt(2, num.get(0));
                ps.setInt(3, num.get(1));
                ps.setInt(4, num.get(2));
                ps.setInt(5, num.get(3));
                ps.setInt(6, num.get(4));
                ps.setInt(7, num.get(5));
                ps.setInt(8, num.get(6));
            }

            @Override
            public int getBatchSize() {
                return nums.size();
            }
        });

        return true;
    }

    public boolean queryBuy(String now) {
        String sql = "SELECT COUNT(*) FROM buy_record WHERE buy_date = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, now);
        return count > 0;
    }

    public String queryNewBuyDate() {
        String sql = "SELECT MAX(buy_date) FROM buy_record";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    public List<Map<String, Object>> queryNewBuy(String date) {
        String sql = "SELECT CONCAT_WS( ',', red_1, red_2, red_3, red_4, red_5, red_6, blue ) AS cp_code FROM buy_record WHERE buy_date = ?";
        return jdbcTemplate.queryForList(sql, date);
    }

    public void saveKjResult(KjResult cpKj) {
        String lotteryRes = cpKj.getLotteryRes();
        String[] split = lotteryRes.split(",");
        String sql = "INSERT IGNORE INTO lottery_record ( lottery_date, red_1, red_2, red_3, red_4, red_5, red_6, blue ) VALUES (?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, cpKj.getLotteryDate(), split[0], split[1], split[2], split[3], split[4], split[5], split[6]);
    }

    public String queryKjResult(String lotteryDate) {
        try {
            String sql = "SELECT CONCAT_WS( ',', red_1, red_2, red_3, red_4, red_5, red_6, blue ) AS result_code FROM lottery_record WHERE lottery_date = ?";
            return jdbcTemplate.queryForObject(sql, String.class, lotteryDate);
        } catch (Exception e) {
            System.out.println("没有昨日记录");
        }
        return null;
    }

    public List<Map<String, Object>> queryHqOptions() {
        String sql = "SELECT 'red_1' hq, GROUP_CONCAT(tab.c) choice FROM ( SELECT red_1 c, COUNT(red_1) m FROM lottery_record GROUP BY red_1 ORDER BY m DESC LIMIT 10 ) tab " +
                "UNION ALL " +
                "SELECT 'red_2' hq, GROUP_CONCAT(tab.c) choice FROM ( SELECT red_2 c, COUNT(red_2) m FROM lottery_record GROUP BY red_2 ORDER BY m DESC LIMIT 10 ) tab " +
                "UNION ALL " +
                "SELECT 'red_3' hq, GROUP_CONCAT(tab.c) choice FROM ( SELECT red_3 c, COUNT(red_3) m FROM lottery_record GROUP BY red_3 ORDER BY m DESC LIMIT 10 ) tab " +
                "UNION ALL " +
                "SELECT 'red_4' hq, GROUP_CONCAT(tab.c) choice FROM ( SELECT red_4 c, COUNT(red_4) m FROM lottery_record GROUP BY red_4 ORDER BY m DESC LIMIT 10 ) tab " +
                "UNION ALL " +
                "SELECT 'red_5' hq, GROUP_CONCAT(tab.c) choice FROM ( SELECT red_5 c, COUNT(red_5) m FROM lottery_record GROUP BY red_5 ORDER BY m DESC LIMIT 10 ) tab " +
                "UNION ALL " +
                "SELECT 'red_6' hq, GROUP_CONCAT(tab.c) choice FROM ( SELECT red_6 c, COUNT(red_6) m FROM lottery_record GROUP BY red_6 ORDER BY m DESC LIMIT 10 ) tab";

        return jdbcTemplate.queryForList(sql);
    }

    public void clearOptions() {
        String sql = "TRUNCATE TABLE hq_option";
        jdbcTemplate.execute(sql);
    }

    public void addHqOptions(List<Map<String, Object>> options) {
        String sql = "INSERT INTO hq_option (`hq`, `choice_hq`) VALUES (?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> map = options.get(i);
                ps.setString(1, String.valueOf(map.get("hq")));
                ps.setString(2, String.valueOf(map.get("choice")));
            }

            @Override
            public int getBatchSize() {
                return options.size();
            }
        });
    }

    public int currentBlue() {
        String sql = "SELECT blue FROM lottery_record ORDER BY id DESC LIMIT 1";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    public List<Integer> listBlueIds(int blue) {
        String sql = "SELECT id FROM lottery_record WHERE blue = ?";
        return jdbcTemplate.queryForList(sql, Integer.class, blue);
    }

    public List<Map<String, Object>> forecast(List<String> newList) {
        String ids = StringUtils.join(newList, ',');
        String sql = "SELECT blue FROM lottery_record WHERE id IN (" + ids + ")";
        return jdbcTemplate.queryForList(sql);
    }
}
